#!/usr/bin/env node

const log=console.log,
    {con}=require('./common.js');
    const moment = require('moment');


async function getSpots(){
    const sql='select * from city where id<10 order by rand() limit 5;';
    let [rows]=await con.execute(sql);
    return rows
}
async function getSpotsAll(){
    const sql='select * from city where id<10;';
    let [rows]=await con.execute(sql);
    return rows
}

async function getSpotById(id){
    const sql=`select * from spot where city=${id};`;
    let [rows]=await con.execute(sql);
    return rows
}

async function getSpotByIdDetail(id){
    const sql=`select * from spot where id=${id};`;
    let [rows]=await con.execute(sql);
    const sql1=`select value from rate where spotid=${id}`
    let [row]=await con.execute(sql1)
    let obj=rows[0]
    console.log('look here',row)
    let rate=0
    if(rows.length!=0){
        for (let i=0;i<row.length;i++){
            rate=rate+row[i].value
        }
        rate=rate/row.length
    }
    
    obj.rate=rate
    console.log('obj',obj)
    return obj
}

async function getSpotByName(name){
    const sql1=`select id,name from spot where name like'%${name}%';`; 
    let [rows1]=await con.execute(sql1);
    const sql2=`select spot.id,spot.name from spot,city where city.name like '%${name}%' and city.id=spot.city;`
    let [rows2]=await con.execute(sql2);
    rows1=rows1.concat(rows2)
    log(rows1)
    return rows1
}

async function addSpotGone(spotid,userid,time){
    const sql=`insert into gone (userid,spotid,time) values ('${userid}',${spotid},'${time}');`;
    let row=await con.execute(sql)
    
    return row
}

async function rate(userid,spotid,value){
    const sql=`insert into rate values ('${userid}',${spotid},${value});`;
    let row=await con.execute(sql)
    return row
}

async function getSpotGone(userid){
    const sql=`select * from gone where userid='${userid}' group by spotid`;
    let row=await con.execute(sql)
    let spots=row[0]
    let res=[]
    for (let i=0;i<spots.length;i++){
        let dates=[]
        let sql1=`select * from spot where id=${row[0][i].spotid}`;
        let row1=await con.execute(sql1)
        console.log(row1[0])
        let sql2=`select * from gone where userid='${userid}' and spotid=${row[0][i].spotid}`
        let [row2]=await con.execute(sql2)
        console.log('here',row2)
        for(let item of row2){
            let Time = moment(item.time).format('YYYY/MM/DD HH:mm');
            dates.push(Time)
        }
        let timer='timer'
        const obj={...row1[0][0],[timer]:dates}
        res.push(obj)
    }
    console.log(res)
    return res
}

async function checkGone(spotid,userid){
    const sqlCount=`select time from gone where userid='${userid}' and spotid=${spotid};`;
    let [row]=await con.execute(sqlCount);
    for(let item of row){
        let Time = moment(item.time).format('YYYY/MM/DD HH:mm');
        item.time = Time;
        console.log(Time)
    }
    console.log(row)
    const sql1=`select value from rate where userid='${userid}' and spotid=${spotid}; `
    let [rows]=await con.execute(sql1)
    console.log(rows)
    return {gone:row,rate:rows[0]}
}

module.exports={getSpots,getSpotById,getSpotByIdDetail,getSpotByName,addSpotGone,getSpotGone,checkGone,getSpotsAll,rate}